﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.ReportCoordinationPrices
{
    public class DataLoader
    {
        public static void GetData(Reports.ReportCoordinationPrices.DataSet.DataDataTable data, string connectionString, Int32 docId)
        {
            string QueryString =
@"SELECT     vwGoodsDetailed.FullName as [Name], vwInputLines.BatchNumber as Seria, 
(vwGoodsDetailed.ManufacturerName + '  ' + vwGoodsDetailed.CountryName) as Manufacturer, 
vwInputLines.NDSTax, 
vwInputLines.ManufacturerPrice * (CAST(100 AS decimal) / (100 + ISNULL(vwInputLines.NDSTax, 0))) AS ManufacturerPriceNoNDS, 
vwInputLines.ManufacturerPrice AS ManufacturerPraceWithNDS, vwInputLines.ReestrPrice, 
vwInputLines.ReestrDate, vwInputLines.ImportMarkup, 
CAST(0 AS decimal) AS ImportMarkupSum, vwInputLines.SupplierMarkup AS OptMarkup, 
CAST(0 AS decimal) AS OptMarkupSum, CAST(0 AS float) AS AllMarkup, CAST(0 AS decimal) AS AllMarkupSum, 
CAST(0 AS decimal) AS OptNoNDS, CAST(0 AS decimal) AS OptNDS, vwInputLines.SupplierPrice as OptWithNDS, 
vwInputLines.DocQntty AS Qntty, vwInputLines.RetailMarkup AS RetailMarkupPrc, 
CAST(0 AS decimal) AS RetailMarkupSum, CAST(0 AS decimal) AS RetailPriceNoNDS, 
CAST(0 AS float) AS RetauiPriceNSDPrc, CAST(0 AS decimal) AS RetailPriceNDSValue, 
vwInputLines.RetailPrice as RetailPriceWithNDS
FROM  vwInputLines INNER JOIN
vwGoodsDetailed ON vwInputLines.GoodsId = vwGoodsDetailed.Id INNER JOIN
CA.Documents ON vwInputLines.DocId = CA.Documents.Id
WHERE     (CA.Documents.DocTypeId <> 3) AND  (CA.Documents.Id = @DocId)
order by 1";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DocId = new SqlParameter("@DocId", SqlDbType.Int);
                    DocId.Value = docId;
                    command.Parameters.Add(DocId);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
        public static void GetInfo(Reports.ReportCoordinationPrices.DataSet.InfoDataTable info, string connectionString, Int32 docId)
        {
            string QueryString =
@"SELECT CA.Documents.ContragentDocNumber as DocNum, 
CA.Documents.ContragentDocDate as DocDate,
(SELECT Name
FROM  CA.Contragents
WHERE  (Id = 0)) AS AptekaName,
(SELECT  INN
FROM  CA.Contragents AS Contragents_2
WHERE (Id = 0)) AS AptekaINN, Contragents_1.Name AS SellerName, 
Contragents_1.INN AS SellerINN
FROM  CA.Contragents AS Contragents_1 INNER JOIN
CA.Documents ON Contragents_1.Id = CA.Documents.ContragentId 
AND Contragents_1.Id = CA.Documents.ContragentId 
WHERE     (CA.Documents.Id = @DocId)";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DocId = new SqlParameter("@DocId", SqlDbType.Int);
                    DocId.Value = docId;
                    command.Parameters.Add(DocId);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(info);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
